<?php
namespace system\libraries\database;

use system\core\Log;

/**
 * Active Record
 * 
 * @author Steffen Brem <steffenbrem@gmail.com>
 * @package System.Libraries.Database
 */
class ActiveRecord 
{
    /**
     * Stores the PDO instance
     * 
     * @var PDO
     */
    private $_pdo;
    
    /**
     * Fetch Mode
     * 
     * @var PDO constant
     */
    private $_fetchMode = \PDO::FETCH_ASSOC;
    
    /**
     * Stores the PDO Statement
     * 
     * @var PDOStatement
     */
    private $_stmt;
    
    /**
     * Stores all the prepared data
     * 
     * @var array
     */
    private $_prepared = array();
    
    /**
     * Stores the SELECT columns
     * 
     * @var array 
     */
    private $_select = array('*');
    
    /**
     * Stores the FROM tables 
     */
    private $_from = array();
    
    /**
     * Stores all the SET statements
     * 
     * @var array
     */
    private $_set = array();
    
    /**
     * Stores all the WHERE statements
     * 
     * @var array
     */
    private $_where = array();
    
    /**
     * Stores the LIMIT
     * 
     * @var string   
     */
    private $_limit;
    
    /**
     * Stores the ORDER BY
     *
     * @var string 
     */
    private $_orderBy = array();
    
    /**
     * Constructor
     *  
     * @param Adapter $pdo
     */
    public function __construct( $group = NULL )
    {
        $this->_pdo = Adapter::getInstance($group)->getConnection();
    }
    
    /**
     * MySQL Query
     * 
     * Run a MySQL query, it will return a PDOStatement object which you can use.
     * 
     * <pre>
     * $this->db->query("UPDATE users SET username=?", 'username');
     * $this->db->query("SELECT * FROM users WHERE username=? AND password=?", array('username', 'password'));
     * </pre>
     * 
     * @param string $query
     * @param array $data
     * @return PDOStatement
     */
    public function query( $query, $data = array() )
    {
        //redirect($query);
        $this->_stmt = $this->_pdo->prepare( $query );
        
        if ( ! is_array($data) )
        {
            $data = array($data);
        }
        
        if ( count($data) == 0)
        {
            if ( count($this->_prepared) > 0 )
            {
                foreach ( $this->_prepared as $param => $value )
                {
                    if ( is_string($value) || is_bool($value) || is_double($value) || is_integer($value) )
                    {
                        $this->_stmt->bindValue($param, $value);
                    }
                    else
                    {
                        throw new \Exception('Unable to bind value in PDO statement. Data with type "' . gettype($value) . '" is not allowed.');
                    }
                }
                $this->_prepared = array();
            }
            
            $this->_stmt->execute();
        }
        else
        {
            $this->_stmt->execute($data);
        }
        
        return $this->_stmt;
    }
    
    /**
     * Fetch a row from the database. It will return an object containing the
     * data.
     * 
     * Use the ? sign to locate placeholders for your data. You can pass the 
     * data as an array in the second parameter.
     * 
     * Example:
     * <pre>
     * $this->db->fetch("SELECT * FROM users WHERE id=?", array(12));
     * </pre>
     * 
     * @access public
     * @param string $stmt
     * @param array $data
     * @return object 
     */
    public function fetch( $query, $data = array() )
    {
        return $this->query($query, $data)->fetch($this->_fetchMode);
    }
    
    /**
     * Fetch all the rows from the database, first parameter is a normal MySQL statement
     * and the second parameter is all the data as an array.
     * 
     * Example:
     * <pre>
     * $this->db->fetchAll("SELECT * FROM users WHERE username=?", array('your username'));
     * </pre>
     * 
     * @access public
     * @param string $stmt
     * @param array $data
     * @return array 
     */
    public function fetchAll( $query, $data = array() )
    {
        return $this->query($query, $data)->fetchAll($this->_fetchMode);
    }
    
    /**
     * Count (affected) rows
     * 
     * Count all the rows from that table.
     * 
     * If you do not specify the $table parameter, it will return the affected
     * rows of the last stamement.
     * 
     * @param string $table
     * @return int
     */
    public function rowCount( $table = NULL )
    {
        if ( $table )
        {
            $this->_stmt = $this->query('SELECT * FROM `' . $table . '`' . $this->_where());
        
            return $this->_stmt->rowCount();
        }
        
        if ( $this->_stmt )
        {
            return $this->_stmt->rowCount();
        }
        
        return 0;
    }
    
    /**
     * Set the SELECT columns
     * 
     * <pre>
     * $this->db->select('column_1', 'column_2');
     * </pre>
     * 
     * Generates:
     * <pre>
     * SELECT `column_1`, `column_2`
     * </pre>
     * 
     * You can also pass an array to this method:
     * <pre>
     * $this->db->select(array('column_1', 'column_2')); 
     * 
     * @access public 
     */
    public function select()
    {
        $args = func_get_args();
        $select = array();
        foreach ( $args as $key => $value )
        {
            if ( is_array($value) )
            {
                foreach ( $value as $column )
                {
                    $select[] = '`' . (string)$column . '`';
                }
            }
            else
            {
                $select[] = '`' . (string)$value . '`';
            }
        }
        if ( count($args) == 0 )
        {
            $select = array('*');
        }
        $this->_select = $select;
    }
    
    /**
     * Generate SELECT string
     * 
     * @access private
     * @return string 
     */
    private function _select()
    {
        $select = $this->_select;
        $this->_select = array('*');
        return (string)implode(', ', $select);
    }
    
    /**
     * Add a table or tables to the FROM statement.
     * 
     * <pre>
     * $this->db->from('table_1', 'table_2');
     * </pre>
     * 
     * Generates:
     * <pre>
     * FROM `table_1`, `table_2`
     * </pre>
     * 
     * You can also pass an array to the from() method.
     * 
     * <pre>
     * $this->db->from(array('table_1', 'table_2'));
     * </pre>
     */
    public function from()
    {
        $args = func_get_args();
        $from = array();
        foreach ( $args as $table )
        {
            if ( is_array($table) )
            {
                foreach ( $table as $aTable )
                {
                    $from[] = '`' . (string)$aTable . '`';
                }
            }
            else
            {
                $from[] = '`' . (string)$table . '`';
            }
        }
        $this->_from = $from;
    }
    
    /**
     * Generate the FROM statement
     * 
     * @access private
     * @return string
     */
    private function _from()
    {
        $from = $this->_from;
        $this->_from = array();
        return implode(', ', $from);
    }
    
    /**
     * Assign a value to the fieldname you specified
     * 
     * @param string $field Fieldname
     * @param string $value New value for this fieldname
     */
    public function set( $field = '', $value = '')
    {
        $this->_set[$field] = $value;
    }
    
    /**
     * Add a WHERE statement. You can specify AND, OR and XOR by defining them
     * after the value. You can add comparison operators too (!=, <=, > etc.)
     * <pre>
     * $this->db->where('username !=', 'foo OR');
     * $this->db->where('id', '2');
     * </pre>
     * Outputs:
     * <pre>
     * WHERE `username`!='foo' OR `id`='2'
     * </pre>
     * 
     * @param string $field
     * @param string $value
     */
    public function where( $field, $value )
    {
        $this->_where[] = array(
            'field_name' => $field,
            'value' => $value
        );
    }


    /**
     * Get the complete WHERE statement
     * 
     * @return string
     */
    private function _where()
    {
        $whereStmt = '';     
        $first = TRUE;
        $count = 0;
        foreach ( $this->_where as $key => $where )
        {
            $fieldName = trim($where['field_name']);
            $value = trim($where['value']);
            
            $count++;
            
            $operators = array('!=', '<>', '>', '<', '>=', '<=');
            $comparisonExp = '='; // Default operator
            $valueEnd = substr($fieldName, -2);
            foreach ( $operators as $operator )
            {
                $valueEnd = substr($fieldName, -strlen($operator));
                if ( $valueEnd == $operator )
                {
                    $comparisonExp = $valueEnd;
                    $fieldName = trim(rtrim($fieldName, $valueEnd));
                }
            }
            
            $operators = array(' OR', ' XOR');
            $operatorExp = ' AND';
            foreach ( $operators as $operator )
            {
                $valueEnd = substr( $value, -strlen( $operator ) );
                if ( in_array($valueEnd, $operators) )
                {
                    $operatorExp = $operator;
                    $value = trim(rtrim($value, $operator));
                }
            }
            
            $operatorExp = (count($this->_where) <= $count) ? '' : $operatorExp;
            
            $whereStmt .= ( $first === TRUE )
                ? ' WHERE `'  . $fieldName . '`' . $comparisonExp . ':w_' . $fieldName . '_' . $key . $operatorExp
                : ' `'  . $fieldName . '`' . $comparisonExp . ':w_' . $fieldName  . '_' . $key . $operatorExp;
            $this->_prepared[':w_'.$fieldName . '_' . $key] = $value;
            $first = FALSE;
        }
        
        // Clear the where statement
        $this->_where = array();
        
        return $whereStmt;
    }
    
    /**
     * Set the LIMIT for the results.
     * 
     * The following will only get 10 rows:
     * <pre>
     * $this->db->limit(10);
     * </pre>
     * 
     * You can also set an offset. The following will get 10 rows with an 
     * offset of 5:
     * <pre>
     * $this->db->limit(10, 5);
     * </pre>
     * 
     * @access public
     * @param int $total
     * @param int $offset
     */
    public function limit( $total = 1, $offset = 0 )
    {
        if ( !is_numeric($total) || !is_numeric($offset) )
        {
            throw new \Exception('One or more parameters given to method "limit()" are not numeric.');
        }
        
        $this->_limit = ($offset > 0) ? $total . ', ' . $offset : $total;
    }
    
    /**
     * Generate the LIMIT statement
     * 
     * @access private
     * @return string
     */
    private function _limit()
    {
        return ($this->_limit) ? ' LIMIT ' . $this->_limit : '';
    }
    
    /**
     * Order results
     * 
     * Usage:
     * <pre>
     * $this->db->orderBy('username DESC');
     * </pre>
     * 
     * You can pass multiple fieldnames if you want:
     * <pre>
     * $this->db->orderBy('last_name', 'first_name DESC');
     * </pre>
     * 
     * We can leave the ASC away after 'last_name', because it is default.
     */
    public function orderBy()
    {
        $args = func_get_args();
        
        if ( count($args) == 0 ) { return; }
        
        foreach ( $args as $arg )
        {
            $arg = trim($arg);
            
            $explode = explode(' ', $arg);
            
            if ( ! isset($explode[1]) )
            {
                $explode[1] = 'ASC';
            }
            
            $this->_orderBy[] = array(
                'field' => $explode[0],
                'order' => $explode[1]
            );
        }
    }
    
    private function _orderBy()
    {
        if ( count($this->_orderBy) == 0 ) { return; }
        
        $string = '';
        foreach( $this->_orderBy as $orderBy )
        {
            $string .= '`' . $orderBy['field'] . '` ' . $orderBy['order'] . ', ';
        }
        $string = rtrim(trim($string), ',');
        
        return ' ORDER BY ' . $string;
    }
    
    /**
     * Update Rows in the Database
     * 
     * You dont need to use the second parameter if you use the Database::set()
     * method. If you pass the data to the second parameter, do this like so:
     * 
     * <pre>
     * Database::update('tablename', array('fieldname'=> 'new_value');
     * </pre>
     * 
     * NOTE: We recommend to use the Database::set() method to assign values to
     * fieldnames! If you use this, do not specify the second parameter.
     * 
     * @access public
     * @param string  $table  The table name to update  
     * @param array   $data   array('fieldname' => 'new_value
     */
    public function update( $table = '', $data = array() )
    {
        $update = '';
        $first = TRUE;
        $preparedData = array();
        
        if ( ! $table )
        {
            throw new \Exception('You have to specify a table name in order to use the update() method.');
        }
        
        if ( ! is_array($data) )
        {
            throw new \Exception('Cannot update table "' . $table . '", given data is not valid.');
        }
        
        if ( count($this->_set) )
        {
            foreach ( $this->_set as $key => $value )
            {  
                $update .= ( $first === TRUE ) 
                ? '`' . $key . '`=:s_'.$key
                : ', `' . $key . '`=:s_'.$key;
                
                $this->_prepared[':s_'.$key] = $value;
                
                $first = FALSE;  
            }   

            $this->query('UPDATE `' . $table . '` SET ' . $update .$this->_where() . $this->_limit());
        }
        else if ( count($data) )
        {
            foreach( $data as $key => $value )
            {
                $update .= ( $first === TRUE )
                ? '`' . $key . '`=?'
                : ', `' . $key . '`=?';
                
                $preparedData[] = $value;
                
                $first = FALSE;
            }
            $this->query('UPDATE `' . $table . '` SET ' . $update . $this->_where() . $this->_limit(), $preparedData);
        }
        else
        {
            throw new \Exception('Cannot update table "' . $table . '", no data was set.');
        }
    }
    
    
    /**
     * Insert a new row in the Database
     * 
     * @access public
     * @param string $table
     * @param array $insert
     */
    public function insert( $table, $insert = array() )
    {
        $fields = '';
        $values = '';
        if ( ! is_array($insert) )
        {
            throw new \Exception('Cannot insert data in "' . $table . '" table, the given data is not an array.');
        }
        
        if ( count($this->_set) )
        {
            foreach ( $this->_set as $key => $value )
            {  
                $fields     .= '`' . $key . '`, ';
                $values     .= ':' . $key . ', ';
                
                $this->_prepared[':'.$key] = $value;
            }
            
            $fields = rtrim($fields, ', ');
            $values = rtrim($values, ', ');

            $this->query('INSERT INTO `' . $table . '` (' . $fields . ') VALUES (' . $values . ')');
        }
        else
        {
            foreach ( $insert as $key => $value )
            {
                $fields     .= '`' . $key . '`, ';
                $values     .= ':' . $key . ', ';

                $this->_prepared[':'.$key] = $value;
            }

            $fields = rtrim($fields, ', ');
            $values = rtrim($values, ', ');

            //echo 'INSERT INTO `' . $table . '` (' . $fields . ') VALUES (' . $values . ')';
            $this->query('INSERT INTO `' . $table . '` (' . $fields . ') VALUES (' . $values . ')');
        }
    }
    
    /**
     * Delete a row from the Database
     * 
     * @access public
     * @param string $table
     */
    public function delete( $table = NULL )
    {
        $from = ($table) ? $table : $this->_from();
        $this->query('DELETE FROM ' . $from . $this->_where() . $this->_limit());
    }
    
    /**
     * Peforms the query and sets the PDOStatement
     * 
     * You can chain the result() or row() methods to fetch the results.
     * 
     * <pre>
     * $this->db->get('table_name')->result();
     * </pre>
     * 
     * @param string $table optional
     * @return \System\Libraries\Database\ActiveRecord 
     */
    public function get( $table = NULL )
    {
        $from = ($table) ? $table : $this->_from(); 
        
        $this->_stmt = $this->query('SELECT ' . $this->_select() . ' FROM `' . $from . '`' . 
                $this->_where() . 
                $this->_limit() .
                $this->_orderBy());
        return $this;
    }
    
    /**
     * Fetch all the results
     * 
     * @return mixed
     */
    public function result()
    {
        return $this->_stmt->fetchAll($this->_fetchMode);
    }
    
    /**
     * Fetch one row
     * 
     * @return mixed 
     */
    public function row()
    {
        return $this->_stmt->fetch($this->_fetchMode);
    }
    
    /**
     * Returns the last inserted Id.
     * 
     * @return int
     */
    public function insertId()
    {
        return $this->_pdo->lastInsertId();
    }
}

/* End of file ActiveRecord.php */